﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace CMS.Class
{
    public class clsDataTable
    {
        public clsDataTable()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        //bien toan cuc
        private static String strConnec = Constant.Connec;
        /// <summary>
        /// Hàm lấy thông tin của 1 record theo ID
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        /// 


        public static DataTable GetItem(String procName, int ID)
        {

            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec _Kn = null;
            _Kn = new QpWebConnec.QpConnec(strConnec);
            Kq = _Kn.GetData(procName, new SqlParameter("@ID", ID));
            return Kq;
        }

        /// <summary>
        /// Hàm này dùng để lấy giá trị của tháng làm việc hiện tại
        /// </summary>
        /// <returns></returns>
        public static DataTable GetYearMonth()
        {
            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec _Kn = null;
            _Kn = new QpWebConnec.QpConnec(strConnec);
            Kq = _Kn.GetData("select * from KHTInfo", CommandType.Text);
            return Kq;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataTable GetItem(String procName, String ID)
        {
            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            Kq = cls.GetData(procName, new SqlParameter("@ID", ID));
            return Kq;
        }

        /// <summary>
        /// Hàm này dùng lấy giá trị của cột mà mình xét theo điều kiện để trả về 1 record thỏa mãn tồn tại trong cơ sở dữ liệu
        /// </summary>
        /// <param name="strTableName">Tên bảng cần xét</param>
        /// <param name="Col">Tên cột so sánh</param>
        /// <param name="key">Giá trị của cột được so sánh</param>
        /// <returns></returns>
        public static DataTable GetItem(String strTableName, String Col, String key)
        {
            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            String sql = "select * from " + strTableName + " where " + Col + "=@" + Col;
            Kq = cls.GetData(Col, key, sql, CommandType.Text);

            return Kq;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strTable"></param>
        /// <param name="parameter"></param>
        /// <param name="values"></param>
        /// <param name="sql"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        public static DataTable GetItemCache(string strTable, String[] parameter, Object[] values, String sql, int time)
        {
            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            Kq = cls.GetCache(strTable, parameter, values, sql, CommandType.Text, time);
            return Kq;
        }

        /// <summary>
        /// Lấy danh sách dữ liệu của 1 bảng
        /// </summary>
        /// <param name="strTable"></param>
        /// <param name="sql"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        public static DataTable GetDataCache(string strTable, String sql, int time)
        {
            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            Kq = cls.GetCache(strTable, sql, time);
            return Kq;
        }

        /// <summary>
        /// Hàm này dùng đọc 1 Proc được viết trong sql.Gọi tên hàm để lấy tất cả dữ liệu của 1 bảng
        /// </summary>
        /// <param name="procName">Tên Proc</param>
        /// <returns></returns>
        public static DataTable GetData(String procName)
        {
            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);

            Kq = cls.GetData(procName);
            return Kq;
        }

        /// <summary>
        /// Hàm này lấy tất cả dữ liệu có trong 1 bảng.Dùng câu truy vấn.Do đó khi gọi hàm nhớ truyền tham số thứ 2 là:CommandType.Text
        /// </summary>
        /// <param name="strTableName">Tên bảng</param>
        /// <param name="commandType">Giá trị là CommandType.Text</param>
        /// <returns></returns>
        public static DataTable GetData(String strTableName, CommandType commandType)
        {

            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            String sql = "select * from " + strTableName;
            Kq = cls.GetData(sql, commandType);
            return Kq;
        }

        /// <summary>
        /// Truyền tên cột để lấy số thứ tự của record
        /// </summary>
        /// <param name="Col">Tên Cột</param>
        /// <param name="strTableName">Tên bảng dữ liệu</param>
        /// <param name="commandType">CommandType.Text</param>
        /// <returns></returns>
        public static DataTable GetData(String Col, String strTableName, CommandType commandType)
        {

            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            String sql = "select ROW_NUMBER() OVER (ORDER BY [" + Col + "]) AS RowNo,* from " + strTableName;
            Kq = cls.GetData(sql, commandType);
            return Kq;
        }

        /// <summary>
        /// Lấy danh sách và sắp xếp theo thứ tự
        /// </summary>
        /// <param name="Col"></param>
        /// <param name="strTableName"></param>
        /// <param name="commandType"></param>
        /// <param name="ColOrDerby"></param>
        /// <returns></returns>
        public static DataTable GetData(String Col, String strTableName, CommandType commandType, String ColOrDerby, int i)
        {

            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            String sql = "";// "select ROW_NUMBER() OVER (ORDER BY [" + Col + "]) AS RowNo,* from " + strTableName + "order by " + ColOrDerby;
            if (i == 0)
            {
                sql = "select ROW_NUMBER() OVER (ORDER BY [" + Col + "]) AS RowNo,* from " + strTableName + " order by " + ColOrDerby;
            }
            else
            {
                sql = "select ROW_NUMBER() OVER (ORDER BY [" + Col + "]) AS RowNo,* from " + strTableName + " order by " + ColOrDerby + " desc";
            }
            Kq = cls.GetData(sql, commandType);
            return Kq;
        }

        /// <summary>
        /// Hàm lấy tham số là câu lệnh query
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>àm
        public static DataTable getData(String sql, CommandType commandType)
        {

            DataTable Kq = new DataTable();
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);


            Kq = cls.GetData(sql, commandType);
            return Kq;
        }

        /// <summary>
        /// Hàm này dùng phân trang trong cơ sở dữ liệu
        /// 
        /// </summary>
        /// <param name="commmandText"></param>
        /// <param name="startPage"></param>
        /// <param name="pageLength"></param>
        /// <param name="noOfPages"></param>
        /// <returns></returns>

        public static DataTable GetData(String commmandText, int startPage, int pageLength, ref int noOfPages)
        {
            DataTable m_Tb;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData(commmandText, startPage, pageLength, ref noOfPages);
            return m_Tb;
        }


        public static DataTable GetData()
        {
            DataTable m_Tb;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData("p_LoadYahoo");

            return m_Tb;
        }
        public static DataTable GetData(String parameter, Object value, String commandText, CommandType commandType)
        {
            DataTable m_Tb;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData(parameter, value, commandText, commandType);
            return m_Tb;
        }

        //p_LoadDanhMuc

        public static DataTable GetDanhMuc()
        {
            DataTable m_Tb;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData("p_LoadDanhMuc");
            return m_Tb;

        }

        /// <summary>
        /// Get danh sanh menu Item
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public static DataTable GetMenuPublic(String Code)
        {
            DataTable m_Tb;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData("p_LoadMenuPublic",
                 new SqlParameter("@CODE", Code));
            return m_Tb;

        }
        //Load Item menu
        /// <summary>
        /// Load Item menu
        /// </summary>
        /// <param name="User"></param>
        /// <param name="Code"></param>
        /// <returns></returns>
        public static DataTable GetDanhMuc(String User, String Code)
        {
            DataTable m_Tb;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData("p_MenuItem_web",
                new SqlParameter("@GROUPNAME", User), new SqlParameter("@CODE", Code));
            return m_Tb;

        }

        //load datat lkvt

        public static DataTable GetLKVT(String proName, int NAM, int THANG)
        {
            DataTable m_Tb;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData(proName,
                new SqlParameter("@NAM", NAM), new SqlParameter("@THANG", THANG));
            return m_Tb;

        }

        /// <summary>
        /// Đăng nhập
        /// </summary>
        /// <param name="User"></param>
        /// <param name="Pass"></param>
        /// <param name="Group"></param>
        /// <returns></returns>
        public static Boolean LogIn(String User, String Pass, ref String Group)
        {
            Boolean bBool = false;
            DataTable m_Tb = null;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            m_Tb = cls.GetData("p_LogIn",
                new SqlParameter("@USERNAME", User), new SqlParameter("@PASS", Pass));
            if (m_Tb.Rows.Count > 0)
            {
                Group = m_Tb.Rows[0][0].ToString().Trim();
                bBool = true;
            }

            return bBool;
        }

        /// <summary>
        /// Đổi mật khẩu
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="Pass"></param>
        /// <returns></returns>
        public static Boolean ChangePass(String userName, String Pass)
        {

            Boolean bBool = false;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            bBool = cls.ExecuteNonQuery("p_DoiPass", new SqlParameter("@PASS", Pass),
                new SqlParameter("@USERNAME", userName));
            return bBool;
        }

        /// <summary>
        /// Đọc danh sách quyền của nhóm
        /// </summary>
        /// <param name="group"></param>
        /// <returns></returns>
        public static DataTable GetPhanQuyen(int group)
        {
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            DataTable tb;
            tb = cls.GetData("p_PhanQuyen", new SqlParameter("@GROUPNAME", group));
            return tb;
        }

        /// <summary>
        /// Đọc phần phân quyền
        /// </summary>
        /// <param name="Col"></param>
        /// <param name="GroupName"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public static Boolean GetPhanQuyen(String Col, int GroupName, String path)
        {
            Boolean bBool = false;
            QpWebConnec.QpConnec cls = null;
            cls = new QpWebConnec.QpConnec(strConnec);
            DataTable tb;
            tb = cls.GetData("p_GetQuyen", new SqlParameter("@GROUPNAME", GroupName),
                new SqlParameter("@Path", path));

            if (tb.Rows.Count > 0)
            {
                try
                {
                    bBool = Convert.ToBoolean(tb.Rows[0][Col]);
                }
                catch
                {
                }
            }

            return bBool;
        }

    }
}